libname temp 'R:\Active\mcbs\SAS\data\temp'; /* save temporary datasets here */
*********************************************************************************************;
*** Need to update YEAR and YR in this macro for years 2006 thru 2013 and run it for steps #1-11     ;
%LET YEAR=2015;
%LET YR=15;

/* these are the paths to the mcbs survey data by year */
options nofmterr; /* run this so I can open the data without the irritating-ass formats */
libname svy_&YR.s "R:\Active\mcbs\&YEAR.\survey\Data\SAS Files"; 

/* these are the paths to the mcbs claims data by year */
libname claims&YR. "R:\Active\mcbs\&YEAR.\survey\Data\Research Claims\SAS Files"; 


/* Infiling the CMS Provider of Service Annual December File Releases to Create a List of FQHC and RHC Facility IDs 
  see: https://www.cms.gov/Research-Statistics-Data-and-Systems/Downloadable-Public-Use-Files/Provider-of-Services/index.html
  note: if PRVDR_CTGRY_CD = '21' then FQHC=1; if PRVDR_CTGRY_CD = '12' then RHC=1; */

/* infiling RHC and FQHC Facility IDs I Downloaded From HRSA in text files */
Data POS_&YEAR. (keep = PRVDR_NUM ZIP_CD FQHC RHC);
	INFILE "R:\Active\mcbs\SAS\cms_pos_facnos\POS_OTHER_DATA_DEC&YR..txt" 
             TRUNCOVER FIRSTOBS = 1 lrecl=32760;
	INPUT

         @1   PRVDR_CTGRY_SBTYP_CD              $CHAR2.	
         @3   PRVDR_CTGRY_CD                    $CHAR2.	
         @5   CHOW_CNT                          2.
         @7   CHOW_DT                           $CHAR8.	
        @15   CITY_NAME                         $CHAR28.	
        @43   ACPTBL_POC_SW                     $CHAR1.	
        @44   CMPLNC_STUS_CD                    $CHAR1.	
        @45   SSA_CNTY_CD                       $CHAR3.	
        @48   CROSS_REF_PROVIDER_NUMBER         $CHAR10.	
        @58   CRTFCTN_DT                        $CHAR8.	
        @66   ELGBLTY_SW                        $CHAR1.	
        @67   FAC_NAME                          $CHAR50.	
       @117   INTRMDRY_CARR_CD                  $CHAR5.	
       @122   MDCD_VNDR_NUM                     $CHAR15.	
       @137   ORGNL_PRTCPTN_DT                  $CHAR8.	
       @145   CHOW_PRIOR_DT                     $CHAR8.	
       @153   INTRMDRY_CARR_PRIOR_CD            $CHAR5.	
       @158   PRVDR_NUM                         $CHAR10.	
       @168   RGN_CD                            $CHAR2.	
       @170   SKLTN_REC_SW                      $CHAR1.	
       @171   STATE_CD                          $CHAR2.	
       @173   SSA_STATE_CD                      $CHAR2.	
       @175   STATE_RGN_CD                      $CHAR3.	
       @178   ST_ADR                            $CHAR50.	
       @228   PHNE_NUM                          $CHAR10.	
       @238   PGM_TRMNTN_CD                     $CHAR2.	
       @240   TRMNTN_EXPRTN_DT                  $CHAR8.	
       @248   CRTFCTN_ACTN_TYPE_CD              $CHAR1.	
       @249   GNRL_CNTL_TYPE_CD                 $CHAR2.	
       @251   ZIP_CD                            $CHAR5.	
       @256   FIPS_STATE_CD                     $CHAR2.	
       @258   FIPS_CNTY_CD                      $CHAR3.	
       @261   CBSA_URBN_RRL_IND                 $CHAR1.	
       @262   CBSA_CD                           $CHAR5.	
       @267   ACRDTN_EFCTV_DT                   $CHAR8.	
       @275   ACRDTN_EXPRTN_DT                  $CHAR8.	
       @283   ACRDTN_TYPE_CD                    $CHAR1.	
       @284   TOT_AFLTD_AMBLNC_SRVC_CNT         2.
       @286   TOT_AFLTD_ASC_CNT                 2.
       @288   TOT_COLCTD_HOSP_CNT               2.
       @290   TOT_AFLTD_ESRD_CNT                2.
       @292   TOT_AFLTD_FQHC_CNT                2.
       @294   TOT_AFLTD_HHA_CNT                 2.
       @296   TOT_AFLTD_HOSPC_CNT               2.
       @298   TOT_AFLTD_OPO_CNT                 2.
       @300   TOT_AFLTD_PRTF_CNT                2.
       @302   TOT_AFLTD_RHC_CNT                 2.
       @304   TOT_AFLTD_SNF_CNT                 2.
       @306   AFLTD_PRVDR_CNT                   2.
       @308   RSDNT_PGM_ALPTHC_SW               $CHAR1.	
       @309   RSDNT_PGM_DNTL_SW                 $CHAR1.	
       @310   RSDNT_PGM_OSTPTHC_SW              $CHAR1.	
       @311   RSDNT_PGM_OTHR_SW                 $CHAR1.	
       @312   RSDNT_PGM_PDTRC_SW                $CHAR1.	
       @313   LAB_SRVC_CD                       $CHAR1.	
       @314   PHRMCY_SRVC_CD                    $CHAR1.	
       @315   RDLGY_SRVC_CD                     $CHAR1.	
       @316   ASC_BGN_SRVC_DT                   $CHAR8.	
       @324   FREESTNDNG_ASC_SW                 $CHAR1.	
       @325   OVRRD_BED_CNT_SW                  $CHAR1.	
       @326   CRTFD_BED_CNT                     4.
       @330   ICFIID_BED_CNT                    4.
       @334   MDCD_NF_BED_CNT                   4.
       @338   MDCR_SNF_BED_CNT                  4.
       @342   MDCR_MDCD_SNF_BED_CNT             4.
       @346   AIDS_BED_CNT                      3.
       @349   ALZHMR_BED_CNT                    3.
       @352   DLYS_BED_CNT                      3.
       @355   DSBL_CHLDRN_BED_CNT               3.
       @358   HEAD_TRMA_BED_CNT                 3.
       @361   HOSPC_BED_CNT                     3.
       @364   HNTGTN_DEASE_BED_CNT              3.
       @367   REHAB_BED_CNT                     3.
       @370   VNTLTR_BED_CNT                    3.
       @373   BED_CNT                           4.
       @377   BRNCH_CNT                         3.
       @380   BRNCH_OPRTN_SW                    $CHAR1.	
       @381   CAH_PSYCH_DPU_SW                  $CHAR1.	
       @382   CAH_REHAB_DPU_SW                  $CHAR1.	
       @383   CAH_SB_SW                         $CHAR1.	
       @384   CRDC_CTHRTZTN_PRCDR_ROOMS_CNT     4.
       @388   GNRL_FAC_TYPE_CD                  $CHAR2.	
       @390   CHOW_SW                           $CHAR1.	
       @391   CLIA_ID_NUMBER_1                  $CHAR10.	
       @401   CLIA_ID_NUMBER_2                  $CHAR10.	
       @411   CLIA_ID_NUMBER_3                  $CHAR10.	
       @421   CLIA_ID_NUMBER_4                  $CHAR10.	
       @431   CLIA_ID_NUMBER_5                  $CHAR10.	
       @441   COLCTN_STUS_SW                    $CHAR1.	
       @442   RN_24_HR_WVR_SW                   $CHAR1.	
       @443   RN_7_DAY_WVR_SW                   $CHAR1.	
       @444   BED_PER_ROOM_WVR_SW               $CHAR1.	
       @445   LSC_WVR_SW                        $CHAR1.	
       @446   ROOM_SIZE_WVR_SW                  $CHAR1.	
       @447   ENDSCPY_PRCDR_ROOMS_CNT           4.
       @451   ESRD_NTWRK_NUM                    $CHAR2.	
       @453   EXPRMT_RSRCH_CNDCTD_SW            $CHAR1.	
       @454   FAX_PHNE_NUM                      $CHAR10.	
       @464   FY_END_MO_DAY_CD                  $CHAR4.	
       @468   FQHC_APPROVED_RHC_PROVIDER_NUM    $CHAR10.	
       @478   FED_FUNDD_FQHC_SW                 $CHAR1.	
       @479   HHA_QLFYD_OPT_SPCH_SW             $CHAR1.	
       @480   HH_AIDE_TRNG_PGM_CD               $CHAR1.	
       @481   HOME_TRNG_SPRT_ONLY_SRVC_SW       $CHAR1.	
       @482   MDCR_HOSPC_SW                     $CHAR1.	
       @483   HOSP_BSD_SW                       $CHAR1.	
       @484   INCNTR_NCTRNL_SRVC_SW             $CHAR1.	
       @485   LTC_CROSS_REF_PROVIDER_NUMBER     $CHAR10.	
       @495   MDCL_SCHL_AFLTN_CD                $CHAR1.	
       @496   MEDICARE_HOSPICE_PROVIDER_NUM     $CHAR10.	
       @506   MDCD_MDCR_PRTCPTG_PRVDR_SW        $CHAR1.	
       @507   MEDICARE_MEDICAID_PRVDR_NUMBER    $CHAR6.	
       @513   MLT_FAC_ORG_NAME                  $CHAR38.	
       @551   MLT_OWND_FAC_ORG_SW               $CHAR1.	
       @552   MEET_1861_SW                      $CHAR1.	
       @553   NPP_TYPE_CD                       $CHAR1.	
       @554   TOT_OFSITE_CNCR_HOSP_CNT          4.
       @558   TOT_OFSITE_CHLDRN_HOSP_CNT        4.
       @562   TOT_OFSITE_EMER_DEPT_CNT          4.
       @566   TOT_OFSITE_INPTNT_LCTN_CNT        4.
       @570   TOT_OFSITE_LTC_HOSP_CNT           4.
       @574   TOT_OFSITE_OPTHLMC_SRGRY_CNT      4.
       @578   TOT_OFSITE_OTHR_LCTN_CNT          4.
       @582   TOT_OFSITE_PSYCH_HOSP_CNT         4.
       @586   TOT_OFSITE_PSYCH_UNIT_CNT         4.
       @590   TOT_OFSITE_REHAB_HOSP_CNT         4.
       @594   TOT_OFSITE_REHAB_UNIT_CNT         4.
       @598   TOT_OFSITE_URGNT_CARE_CNTR_CNT    4.
       @602   OFSITE_LCTN_CNT                   3.
       @605   OPRTG_ROOM_CNT                    4.
       @609   ORGNZ_FMLY_MBR_GRP_SW             $CHAR1.	
       @610   ORGNZ_RSDNT_GRP_SW                $CHAR1.	
       @611   PARENT_PROVIDER_NUMBER            $CHAR10.	
       @621   FQHC_APRVD_RHC_SW                 $CHAR1.	
       @622   MDCR_PRTCPTN_OP_PT_SPCH_SW        $CHAR1.	
       @623   PGM_PRTCPTN_CD                    $CHAR1.	
       @624   PRVDR_BSD_FAC_SW                  $CHAR1.	
       @625   PRVNC_CD                          $CHAR2.	
       @627   PSYCH_UNIT_BED_CNT                3.
       @630   PSYCH_UNIT_EFCTV_DT               $CHAR8.	
       @638   PSYCH_UNIT_SW                     $CHAR1.	
       @639   PSYCH_UNIT_TRMNTN_CD              $CHAR1.	
       @640   PSYCH_UNIT_TRMNTN_DT              $CHAR8.	
       @648   REHAB_UNIT_BED_CNT                3.
       @651   REHAB_UNIT_EFCTV_DT               $CHAR8.	
       @659   REHAB_UNIT_SW                     $CHAR1.	
       @660   REHAB_UNIT_TRMNTN_CD              $CHAR1.	
       @661   REHAB_UNIT_TRMNTN_DT              $CHAR8.	
       @669   RELATED_PROVIDER_NUMBER           $CHAR10.	
       @679   ACUTE_RNL_DLYS_SRVC_CD            $CHAR1.	
       @680   PSYCH_SRVC_CD                     $CHAR1.	
       @681   HH_AIDE_SRVC_CD                   $CHAR1.	
       @682   ALCHL_DRUG_SRVC_CD                $CHAR1.	
       @683   ANSTHSA_SRVC_CD                   $CHAR1.	
       @684   APLNC_EQUIP_SRVC_CD               $CHAR1.	
       @685   AUDLGY_SRVC_CD                    $CHAR1.	
       @686   BLOOD_SRVC_OFSITE_RSDNT_SW        $CHAR1.	
       @687   BLOOD_SRVC_ONST_NRSDNT_SW         $CHAR1.	
       @688   BLOOD_SRVC_ONST_RSDNT_SW          $CHAR1.	
       @689   BURN_CARE_UNIT_SRVC_CD            $CHAR1.	
       @690   CRDC_CTHRTZTN_LAB_SRVC_CD         $CHAR1.	
       @691   OPEN_HRT_SRGRY_SRVC_CD            $CHAR1.	
       @692   CARF_IP_REHAB_SRVC_CD             $CHAR1.	
       @693   CHMTHRPY_SRVC_CD                  $CHAR1.	
       @694   CHRPRCTIC_SRVC_CD                 $CHAR1.	
       @695   CL_SRVC_OFSITE_RSDNT_SW           $CHAR1.	
       @696   CL_SRVC_ONST_NRSDNT_SW            $CHAR1.	
       @697   CL_SRVC_ONST_RSDNT_SW             $CHAR1.	
       @698   CL_SRVC_CD                        $CHAR1.	
       @699   CRNRY_CARE_UNIT_SRVC_CD           $CHAR1.	
       @700   CNSLNG_SRVC_CD                    $CHAR1.	
       @701   CT_SCAN_SRVC_CD                   $CHAR1.	
       @702   DNTL_SRVC_CD                      $CHAR1.	
       @703   DNTL_SRVC_OFSITE_RSDNT_SW         $CHAR1.	
       @704   DNTL_SRVC_ONST_NRSDNT_SW          $CHAR1.	
       @705   DNTL_SRVC_ONST_RSDNT_SW           $CHAR1.	
       @706   SHCK_TRMA_SRVC_CD                 $CHAR1.	
       @707   DGNSTC_RDLGY_SRVC_CD              $CHAR1.	
       @708   DTRY_SRVC_CD                      $CHAR1.	
       @709   DTRY_OFSITE_RSDNT_SW              $CHAR1.	
       @710   DTRY_ONST_NRSDNT_SW               $CHAR1.	
       @711   DTRY_ONST_RSDNT_SW                $CHAR1.	
       @712   DCTD_ER_SRVC_CD                   $CHAR1.	
       @713   EMER_PSYCH_SRVC_CD                $CHAR1.	
       @714   XTRCRPRL_SHCK_LTHTRPTR_SRVC_CD    $CHAR1.	
       @715   FRNSC_PSYCH_SRVC_CD               $CHAR1.	
       @716   GRTRC_PSYCH_SRVC_CD               $CHAR1.	
       @717   GRNTLGCL_SPCLTY_SRVC_CD           $CHAR1.	
       @718   SP_HOME_TRNG_SPRT_HD_SW           $CHAR1.	
       @719   HMDLYS_SRVC_SW                    $CHAR1.	
       @720   HMMKR_SRVC_CD                     $CHAR1.	
       @721   HSEKPNG_SRVC_OFSITE_RSDNT_SW      $CHAR1.	
       @722   HSEKPNG_SRVC_ONST_NRSDNT_SW       $CHAR1.	
       @723   HSEKPNG_SRVC_ONST_RSDNT_SW        $CHAR1.	
       @724   IP_SRGCL_SRVC_CD                  $CHAR1.	
       @725   INTRN_RSDNT_SRVC_CD               $CHAR1.	
       @726   MDCL_SCL_SRVC_CD                  $CHAR1.	
       @727   MDCL_SUPLY_SRVC_CD                $CHAR1.	
       @728   ICU_SRVC_CD                       $CHAR1.	
       @729   MDCR_TRNSPLNT_CNTR_SRVC_CD        $CHAR1.	
       @730   MENTL_HLTH_OFSITE_RSDNT_SW        $CHAR1.	
       @731   MENTL_HLTH_ONST_NRSDNT_SW         $CHAR1.	
       @732   MENTL_HLTH_ONST_RSDNT_SW          $CHAR1.	
       @733   MGNTC_RSNC_IMG_SRVC_CD            $CHAR1.	
       @734   NEONTL_ICU_SRVC_CD                $CHAR1.	
       @735   NEONTL_NRSRY_SRVC_CD              $CHAR1.	
       @736   NRSRGCL_SRVC_CD                   $CHAR1.	
       @737   ORGN_TRNSPLNT_SRVC_CD             $CHAR1.	
       @738   NUCLR_MDCN_SRVC_CD                $CHAR1.	
       @739   NRSNG_SRVC_EMPLEE_SW              $CHAR1.	
       @740   NRSNG_SRVC_CNTRCTR_SW             $CHAR1.	
       @741   NRSNG_SRVC_ARNGMT_SW              $CHAR1.	
       @742   NRSNG_SRVC_CD                     $CHAR1.	
       @743   NRSNG_SRVC_OFSITE_RSDNT_SW        $CHAR1.	
       @744   NRSNG_SRVC_ONST_NRSDNT_SW         $CHAR1.	
       @745   NRSNG_SRVC_ONST_RSDNT_SW          $CHAR1.	
       @746   NTRTNL_GDNC_SRVC_CD               $CHAR1.	
       @747   OB_SRVC_CD                        $CHAR1.	
       @748   OPTHLMC_SRGY_SRVC_CD              $CHAR1.	
       @749   OPTMTRC_SRVC_CD                   $CHAR1.	
       @750   OPRTG_ROOM_SRVC_CD                $CHAR1.	
       @751   ORTHPDC_SRGY_SRVC_CD              $CHAR1.	
       @752   ORTHTC_PRSTHTC_EMPLEE_SW          $CHAR1.	
       @753   ORTHTC_PRSTHTC_CNTRCTR_SW         $CHAR1.	
       @754   ORTHTC_PRSTHTC_ARNGMT_SW          $CHAR1.	
       @755   OT_EMPLEE_SW                      $CHAR1.	
       @756   OT_CNTRCTR_SW                     $CHAR1.	
       @757   OT_ARNGMT_SW                      $CHAR1.	
       @758   OT_SRVC_CD                        $CHAR1.	
       @759   OT_SRVC_OFSITE_RSDNT_SW           $CHAR1.	
       @760   OT_SRVC_ONST_NRSDNT_SW            $CHAR1.	
       @761   OT_SRVC_ONST_RSDNT_SW             $CHAR1.	
       @762   OTHR_SRVC_CD                      $CHAR1.	
       @763   OP_SRVC_CD                        $CHAR1.	
       @764   OP_PSYCH_SRVC_CD                  $CHAR1.	
       @765   OP_REHAB_SRVC_CD                  $CHAR1.	
       @766   OP_SRGRY_UNIT_SRVC_CD             $CHAR1.	
       @767   PED_SRVC_CD                       $CHAR1.	
       @768   PED_ICU_SRVC_CD                   $CHAR1.	
       @769   SP_HOME_TRNG_SPRT_PD_SW           $CHAR1.	
       @770   PRTNL_DLYS_SRVC_SW                $CHAR1.	
       @771   PET_SCAN_SRVC_CD                  $CHAR1.	
       @772   PHRMCY_SRVC_OFSITE_RSDNT_SW       $CHAR1.	
       @773   PHRMCY_SRVC_ONST_NRSDNT_SW        $CHAR1.	
       @774   PHRMCY_SRVC_ONST_RSDNT_SW         $CHAR1.	
       @775   PHYSN_EMPLEE_SW                   $CHAR1.	
       @776   PHYSN_CNTRCTR_SW                  $CHAR1.	
       @777   PHYSN_ARNGMT_SW                   $CHAR1.	
       @778   PHYSN_SRVC_CD                     $CHAR1.	
       @779   PHYSN_EXT_SRVC_OFSITE_RSDNT_SW    $CHAR1.	
       @780   PHYSN_EXT_SRVC_ONST_NRSDNT_SW     $CHAR1.	
       @781   PHYSN_EXT_SRVC_ONST_RSDNT_SW      $CHAR1.	
       @782   PHYSN_SRVC_OFSITE_RSDNT_SW        $CHAR1.	
       @783   PHYSN_SRVC_ONST_NRSDNT_SW         $CHAR1.	
       @784   PHYSN_SRVC_ONST_RSDNT_SW          $CHAR1.	
       @785   PDTRY_SRVC_OFSITE_RSDNT_SW        $CHAR1.	
       @786   PDTRY_SRVC_ONST_NRSDNT_SW         $CHAR1.	
       @787   PDTRY_SRVC_ONST_RSDNT_SW          $CHAR1.	
       @788   PSTOPRTV_RCVRY_SRVC_CD            $CHAR1.	
       @789   CHLD_ADLSCNT_PSYCH_SRVC_CD        $CHAR1.	
       @790   PSYCHLGCL_EMPLEE_SW               $CHAR1.	
       @791   PSYCHLGCL_CNTRCTR_SW              $CHAR1.	
       @792   PSYCHLGCL_ARNGMT_SW               $CHAR1.	
       @793   PT_EMPLEE_SW                      $CHAR1.	
       @794   PT_CNTRCTR_SW                     $CHAR1.	
       @795   PT_ARNGMT_SW                      $CHAR1.	
       @796   PT_SRVC_CD                        $CHAR1.	
       @797   PT_OFSITE_RSDNT_SW                $CHAR1.	
       @798   PT_ONST_NRSDNT_SW                 $CHAR1.	
       @799   PT_ONST_RSDNT_SW                  $CHAR1.	
       @800   RCNSTRCTN_SRGY_SRVC_CD            $CHAR1.	
       @801   RSPRTRY_CARE_EMPLEE_SW            $CHAR1.	
       @802   RSPRTRY_CARE_CNTRCTR_SW           $CHAR1.	
       @803   RSPRTRY_CARE_ARNGMT_SW            $CHAR1.	
       @804   RSPRTRY_CARE_SRVC_CD              $CHAR1.	
       @805   SHRT_TERM_IP_SRVC_CD              $CHAR1.	
       @806   SCL_EMPLEE_SW                     $CHAR1.	
       @807   SCL_CNTRCTR_SW                    $CHAR1.	
       @808   SCL_ARNGMT_SW                     $CHAR1.	
       @809   SCL_SRVC_CD                       $CHAR1.	
       @810   SCL_WORK_SRVC_OFSITE_RSDNT_SW     $CHAR1.	
       @811   SCL_WORK_SRVC_ONST_NRSDNT_SW      $CHAR1.	
       @812   SCL_WORK_SRVC_ONST_RSDNT_SW       $CHAR1.	
       @813   SPCH_PTHLGY_EMPLEE_SW             $CHAR1.	
       @814   SPCH_PTHLGY_CNTRCTR_SW            $CHAR1.	
       @815   SPCH_PTHLGY_ARNGMT_SW             $CHAR1.	
       @816   SPCH_PTHLGY_SRVC_CD               $CHAR1.	
       @817   SPCH_PTHLGY_OFSITE_RSDNT_SW       $CHAR1.	
       @818   SPCH_PTHLGY_ONST_NRSDNT_SW        $CHAR1.	
       @819   SPCH_PTHLGY_ONST_RSDNT_SW         $CHAR1.	
       @820   SPCH_THRPY_SRVC_CD                $CHAR1.	
       @821   SRGCL_ICU_SRVC_CD                 $CHAR1.	
       @822   ACTVTY_OTHR_OFSITE_RSDNT_SW       $CHAR1.	
       @823   ACTVTY_OTHR_ONST_NRSDNT_SW        $CHAR1.	
       @824   ACTVTY_OTHR_ONST_RSDNT_SW         $CHAR1.	
       @825   SCL_SRVC_OTHR_OFSITE_RSDNT_SW     $CHAR1.	
       @826   SCL_SRVC_OTHR_ONST_NRSDNT_SW      $CHAR1.	
       @827   SCL_SRVC_OTHR_ONST_RSDNT_SW       $CHAR1.	
       @828   ACTVTY_OFSITE_RSDNT_SW            $CHAR1.	
       @829   ACTVTY_ONST_NRSDNT_SW             $CHAR1.	
       @830   ACTVTY_ONST_RSDNT_SW              $CHAR1.	
       @831   THRPTC_RDLGY_SRVC_CD              $CHAR1.	
       @832   THRPTC_RCRTNL_OFSITE_RSDNT_SW     $CHAR1.	
       @833   THRPTC_RCRTNL_ONST_NRSDNT_SW      $CHAR1.	
       @834   THRPTC_RCRTNL_ONST_RSDNT_SW       $CHAR1.	
       @835   URGNT_CARE_SRVC_CD                $CHAR1.	
       @836   VCTNL_GDNC_SRVC_CD                $CHAR1.	
       @837   VCTNL_SRVC_OFSITE_RSDNT_SW        $CHAR1.	
       @838   VCTNL_SRVC_ONST_NRSDNT_SW         $CHAR1.	
       @839   VCTNL_SRVC_ONST_RSDNT_SW          $CHAR1.	
       @840   DGNSTC_XRAY_OFSITE_RSDNT_SW       $CHAR1.	
       @841   DGNSTC_XRAY_ONST_NRSDNT_SW        $CHAR1.	
       @842   DGNSTC_XRAY_ONST_RSDNT_SW         $CHAR1.	
       @843   ACUTE_RESP_CARE_CD                $CHAR1.	
       @844   OVRRD_STFG_SW                     $CHAR1.	
       @845   PROFNL_ADMIN_CNTRCT_CNT           8.2
       @853   PROFNL_ADMIN_FLTM_CNT             8.2
       @861   PROFNL_ADMIN_PRTM_CNT             8.2
       @869   HH_AIDE_EMPLEE_CNT                8.2
       @877   HH_AIDE_VLNTR_CNT                 8.2
       @885   PRSNEL_OTHR_CNT                   8.2
       @893   NRS_AIDE_CNTRCT_CNT               8.2
       @901   NRS_AIDE_FLTM_CNT                 8.2
       @909   NRS_AIDE_PRTM_CNT                 8.2
       @917   CNSLR_EMPLEE_CNT                  8.2
       @925   CNSLR_VLNTR_CNT                   8.2
       @933   CRNA_CNT                          8.2
       @941   DNTST_CNTRCT_CNT                  8.2
       @949   DNTST_FLTM_CNT                    8.2
       @957   DNTST_PRTM_CNT                    8.2
       @965   DIETN_CNT                         8.2
       @973   DIETN_CNTRCT_CNT                  8.2
       @981   DIETN_FLTM_CNT                    8.2
       @989   DIETN_PRTM_CNT                    8.2
       @997   DRCT_CARE_PRSNEL_CNT              8.2
      @1005   FOOD_SRVC_CNTRCT_CNT              8.2
      @1013   FOOD_SRVC_FLTM_CNT                8.2
      @1021   FOOD_SRVC_PRTM_CNT                8.2
      @1029   HH_AIDE_CNT                       8.2
      @1037   HMMKR_EMPLEE_CNT                  8.2
      @1045   HMMKR_VLNTR_CNT                   8.2
      @1053   HSEKPNG_CNTRCT_CNT                8.2
      @1061   HSEKPNG_FLTM_CNT                  8.2
      @1069   HSEKPNG_PRTM_CNT                  8.2
      @1077   LAB_TCHNCN_CNT                    8.2
      @1085   LPN_CNT                           8.2
      @1093   LPN_LVN_CNT                       8.2
      @1101   LPN_LVN_CNTRCT_CNT                8.2
      @1109   LPN_LVN_FLTM_CNT                  8.2
      @1117   LPN_LVN_PRTM_CNT                  8.2
      @1125   LPN_LVN_VLNTR_CNT                 8.2
      @1133   MDCL_DRCTR_CNTRCT_CNT             8.2
      @1141   MDCL_DRCTR_FLTM_CNT               8.2
      @1149   MDCL_DRCTR_PRTM_CNT               8.2
      @1157   MDCL_SCL_WORKR_CNT                8.2
      @1165   MDCL_SCL_WORKR_VLNTR_CNT          8.2
      @1173   MDCL_TCHNLGST_CNT                 8.2
      @1181   MDCTN_AIDE_CNTRCT_CNT             8.2
      @1189   MDCTN_AIDE_FLTM_CNT               8.2
      @1197   MDCTN_AIDE_PRTM_CNT               8.2
      @1205   MENTL_HLTH_SRVC_CNTRCT_CNT        8.2
      @1213   MENTL_HLTH_SRVC_FLTM_CNT          8.2
      @1221   MENTL_HLTH_SRVC_PRTM_CNT          8.2
      @1229   NUCLR_MDCN_TCHNCN_CNT             8.2
      @1237   NAT_CNTRCT_CNT                    8.2
      @1245   NAT_FLTM_CNT                      8.2
      @1253   NAT_PRTM_CNT                      8.2
      @1261   NRS_PRCTNR_CNT                    8.2
      @1269   NRS_ADMINV_CNTRCT_CNT             8.2
      @1277   NRS_ADMINV_FLTM_CNT               8.2
      @1285   NRS_ADMINV_PRTM_CNT               8.2
      @1293   OCPTNL_THRPST_CNT                 8.2
      @1301   OCPTNL_THRPST_CNTRCT_CNT          8.2
      @1309   OCPTNL_THRPST_FLTM_CNT            8.2
      @1317   OCPTNL_THRPST_PRTM_CNT            8.2
      @1325   OT_AIDE_CNTRCT_CNT                8.2
      @1333   OT_AIDE_FLTM_CNT                  8.2
      @1341   OT_AIDE_PRTM_CNT                  8.2
      @1349   OT_ASTNT_CNTRCT_CNT               8.2
      @1357   OT_ASTNT_FLTM_CNT                 8.2
      @1365   OT_ASTNT_PRTM_CNT                 8.2
      @1373   VLNTR_OTHR_CNT                    8.2
      @1381   ACTVTY_STF_OTHR_CNTRCT_CNT        8.2
      @1389   ACTVTY_STF_OTHR_FLTM_CNT          8.2
      @1397   ACTVTY_STF_OTHR_PRTM_CNT          8.2
      @1405   PHYSN_OTHR_CNTRCT_CNT             8.2
      @1413   PHYSN_OTHR_FLTM_CNT               8.2
      @1421   PHYSN_OTHR_PRTM_CNT               8.2
      @1429   SCL_SRVC_OTHR_STF_CNTRCT_CNT      8.2
      @1437   SCL_SRVC_OTHR_STF_FLTM_CNT        8.2
      @1445   SCL_SRVC_OTHR_STF_PRTM_CNT        8.2
      @1453   STF_OTHR_CNTRCT_CNT               8.2
      @1461   STF_OTHR_FLTM_CNT                 8.2
      @1469   STF_OTHR_PRTM_CNT                 8.2
      @1477   PHRMCST_CNTRCT_CNT                8.2
      @1485   PHRMCST_FLTM_CNT                  8.2
      @1493   PHRMCST_PRTM_CNT                  8.2
      @1501   PHYS_THRPST_CNTRCT_CNT            8.2
      @1509   PHYS_THRPST_FLTM_CNT              8.2
      @1517   PHYS_THRPST_PRTM_CNT              8.2
      @1525   PHYSN_CNT                         8.2
      @1533   PHYSN_VLNTR_CNT                   8.2
      @1541   PHYSN_ASTNT_CNT                   8.2
      @1549   PHYSN_EXT_CNTRCT_CNT              8.2
      @1557   PHYSN_EXT_FLTM_CNT                8.2
      @1565   PHYSN_EXT_PRTM_CNT                8.2
      @1573   RSDNT_PHYSN_CNT                   8.2
      @1581   PDTRST_CNTRCT_CNT                 8.2
      @1589   PDTRST_FLTM_CNT                   8.2
      @1597   PDTRST_PRTM_CNT                   8.2
      @1605   PSYCHLGST_CNT                     8.2
      @1613   PHYS_THRPST_STF_CNT               8.2
      @1621   PHYS_THRPST_CNT                   8.2
      @1629   PHYS_THRPST_ARNGMT_CNT            8.2
      @1637   PT_AIDE_CNTRCT_CNT                8.2
      @1645   PT_AIDE_FLTM_CNT                  8.2
      @1653   PT_AIDE_PRTM_CNT                  8.2
      @1661   PT_ASTNT_CNTRCT_CNT               8.2
      @1669   PT_ASTNT_FLTM_CNT                 8.2
      @1677   PT_ASTNT_PRTM_CNT                 8.2
      @1685   ACTVTY_PROFNL_CNTRCT_CNT          8.2
      @1693   ACTVTY_PROFNL_FLTM_CNT            8.2
      @1701   ACTVTY_PROFNL_PRTM_CNT            8.2
      @1709   RDLGY_TCHNCN_CNT                  8.2
      @1717   REG_PHRMCST_CNT                   8.2
      @1725   INHLTN_THRPST_CNT                 8.2
      @1733   RN_CNT                            8.2
      @1741   RN_CNTRCT_CNT                     8.2
      @1749   RN_FLTM_CNT                       8.2
      @1757   RN_PRTM_CNT                       8.2
      @1765   RN_VLNTR_CNT                      8.2
      @1773   RN_DRCTR_CNTRCT_CNT               8.2
      @1781   RN_DRCTR_FLTM_CNT                 8.2
      @1789   RN_DRCTR_PRTM_CNT                 8.2
      @1797   SCL_WORKR_CNT                     8.2
      @1805   SCL_WORKR_CNTRCT_CNT              8.2
      @1813   SCL_WORKR_FLTM_CNT                8.2
      @1821   SCL_WORKR_PRTM_CNT                8.2
      @1829   SPCH_PTHLGST_ARNGMT_CNT           8.2
      @1837   SPCH_PTHLGST_CNTRCT_CNT           8.2
      @1845   SPCH_PTHLGST_FLTM_CNT             8.2
      @1853   SPCH_PTHLGST_PRTM_CNT             8.2
      @1861   SPCH_PTHLGST_CNT                  8.2
      @1869   SPCH_PTHLGST_AUDLGST_CNT          8.2
      @1877   TCHNCL_STF_NUM                    8.2
      @1885   TCHNCN_CNT                        8.2
      @1893   THRPTC_RCRTNL_CNTRCT_CNT          8.2
      @1901   THRPTC_RCRTNL_FLTM_CNT            8.2
      @1909   THRPTC_RCRTNL_PRTM_CNT            8.2
      @1917   EMPLEE_CNT                        9.2
      @1926   VLNTR_CNT                         9.2
      @1935   SBUNIT_CNT                        3.
      @1938   SBUNIT_SW                         $CHAR1.	
      @1939   SBUNIT_OPRTN_SW                   $CHAR1.	
      @1940   DNTL_SRGRY_SW                     $CHAR1.	
      @1941   OTLRYNGLGY_SRGRY_SW               $CHAR1.	
      @1942   ENDSCPY_SRGRY_SW                  $CHAR1.	
      @1943   OB_GYN_SRGRY_SW                   $CHAR1.	
      @1944   OPTHMLGY_SRGRY_SW                 $CHAR1.	
      @1945   ORTHPDC_SRGRY_SW                  $CHAR1.	
      @1946   OTHR_SRGRY_SW                     $CHAR1.	
      @1947   PAIN_SRGRY_SW                     $CHAR1.	
      @1948   PLSTC_SRGRY_SW                    $CHAR1.	
      @1949   FT_SRGRY_SW                       $CHAR1.	
      @1950   SB_SW                             $CHAR1.	
      @1951   SB_SIZE_CD                        $CHAR1.	
      @1952   TCHNLGST_2_YR_RDLGC_CNT           8.2
      @1960   TCHNLGST_ASCT_DGR_CNT             8.2
      @1968   TCHNLGST_BS_BA_DGR_CNT            8.2
      @1976   DLYS_STN_CNT                      3.
;

if PRVDR_CTGRY_CD = '21' then FQHC=1; else FQHC=0;
if PRVDR_CTGRY_CD = '12' then RHC=1; else RHC=0;
if PRVDR_CTGRY_CD in ('12','21');
Run;

proc sort data = pos_&YEAR. nodupkey; by PRVDR_NUM; run; 

/* Getting needed variables from PHY for E&M visits */
proc sql;
   create table phy_em_&YEAR. as 
   select	b.BASEID		,
			b.CLAIMID		,
			b.LINE_NUM		,
			b.CLM_THRU_DT	,
			b.LINE_ALOWD_CHRG_AMT	,	
			b.PRVDR_SPCLTY		,
			b.BETOS_CD			,
			b.HCPCS_CD		,
			b.LINE_PLACE_OF_SRVC_CD		,
			b.PRVDR_ZIP		,
			b.RFR_PHYSN_NPI		,			
			b.PRF_PHYSN_NPI		,
			b.ICD_DGNS_CD1		,
			b.ICD_DGNS_CD2		,
			b.ICD_DGNS_CD3		,
			b.ICD_DGNS_CD4		,
			b.ICD_DGNS_CD5		,
			b.ICD_DGNS_CD6		,
			b.ICD_DGNS_CD7		,
			b.ICD_DGNS_CD8		,
			b.ICD_DGNS_CD9		,
			b.ICD_DGNS_CD10		,
			b.ICD_DGNS_CD11		,
			b.ICD_DGNS_CD12					
      from claims&YR..phy as b 
		where b.LINE_PRCSG_IND_CD in ('A','S','R') /* keeping only claim line items that were accepted for primary or secondary or final reprocessed payment */	
			and b.HCPCS_CD in ('96160','96161','99201','99202','99203','99204','99205','99211','99212','99213','99214','99215','99304','99305','99306','99307','99308','99309','99310',
							   '99315','99316','99318','99324','99325','99326','99327','99328','99334','99335','99336','99337','99339','99340','99341','99342','99343','99344','99345',
							   '99347','99348','99349','99350','99354','99355','99421','99422','99423','99439','99483','99484','99487','99489','99490','99491','99492','99493','99494',
							   '99495','99496','99497','99498','G0402','G0438','G0439','G0442','G0443','G0444','G0463','G0506','G2010','G2012','G2058','G2064','G2065','G2214','GCOL1') 
							   /* limiting procedure codes above to primary care service codes as defined by MIPS and/or MSSP */
	  order by b.BASEID, b.CLAIMID, b.LINE_NUM, b.CLM_THRU_DT;
quit;

data phy_em_&YEAR.;
set phy_em_&YEAR.; /* per MIPS specs drop all E&M visits with codes below that ocurred in a SNF */
if HCPCS_CD in ('99304','99305','99306','99307','99308','99309','99310','99315','99316','99318') and LINE_PLACE_OF_SRVC_CD='31' then delete;
if HCPCS_CD in ('99497','99498') and LINE_PLACE_OF_SRVC_CD='21' then delete; /* per MSSP specs drop E&M visits with these codes that occurred in inpatient hosp*/
run;

proc sort data = phy_em_&YEAR. nodupkey; /* if there are dupes at this level they can safely be deleted for all years */
by BASEID CLAIMID LINE_NUM PRF_PHYSN_NPI; 
run;

/* Getting needed variables from OTP for RHC/FQHC/CAH E&M visits */
proc sql;
   create table otp_em_&YEAR. as 
   select	b.BASEID		,
			b.CLAIMID		,
			b.CLM_THRU_DT		,
			b.PRVDR_NUM		, /* this is the institutional ID for the FQHC/RHC/CAH - this is what I will have to use - and assume they are primary care */
			b.CLM_FAC_TYPE_CD		,
			b.CLM_SRVC_CLSFCTN_TYPE_CD 		,
			b.PRVDR_STATE_CD		,
			b.AT_PHYSN_NPI			
      from claims&YR..otp as b
      where b.CLAIMID ne .
	  		and b.CLM_MDCR_NON_PMT_RSN_CD='' /* this means the claim was not denied */
	  order by b.BASEID, b.CLAIMID, b.CLM_THRU_DT;
quit;

proc sort data = otp_em_&YEAR.; 
by BASEID CLAIMID; 
run;

proc sort data = claims&YR..otprev; 
by BASEID CLAIMID; 
run;

data otp_em_&YEAR. (keep = BASEID--CLM_THRU_DT HCPCS_CD REV_ALW REV_CNTR PRVDR_NUM PRF_PHYSN_NPI);
merge otp_em_&YEAR. claims&YR..otprev;
by BASEID CLAIMID;
if AT_PHYSN_NPI ne ''; /*member is in the otp file above */
rev=substr(REV_CNTR,1,3);
if REV_CNTR in ('0521','0522','0525') /* RHC/FQHC/CAH identifier */
or rev in ('096','097','098'); /* Method II CAH Claims for professional services */
PRF_PHYSN_NPI = AT_PHYSN_NPI;
REV_ALW = REV_CNTR_TOT_CHRG_AMT - REV_CNTR_NCVRD_CHRG_AMT; /* Revenue center allowed amount = total charged amount minus non-covered charge amount */
run;

proc sort data = otp_em_&YEAR. nodupkey; /* if there are dupes at this level they can safely be deleted for all years */
by BASEID CLAIMID PRF_PHYSN_NPI;
run;

proc sort data = otp_em_&YEAR.;
by PRVDR_NUM;
run;

Data otp_em_&YEAR. (drop = FQHC RHC);
merge otp_em_&YEAR. pos_&YEAR.; /* merging in my database of FQHC and RHC IDs */
by PRVDR_NUM;
if PRF_PHYSN_NPI ne ''; /*member is in the otp file above */
run;

proc sort data = otp_em_&YEAR. nodupkey; /* there shouldn't be any dupes */
by BASEID CLAIMID PRF_PHYSN_NPI;
run;

data phy_em_&YEAR. (drop = LINE_NUM PRF_PHYSN_NPI PRVDR_SPCLTY CLM_THRU_DT LINE_ALOWD_CHRG_AMT REV_ALW ZIP_CD PRVDR_ZIP);
set phy_em_&YEAR. otp_em_&YEAR.;

LINEITEM=LINE_NUM;
PRF_NPI=PRF_PHYSN_NPI; 
HCFASPCL=PRVDR_SPCLTY;
if LINEITEM ne . then em_alw_amt = LINE_ALOWD_CHRG_AMT;
else if LINEITEM = . then em_alw_amt = REV_ALW;

format FROM yymmdd10.;
FROM = CLM_THRU_DT;  
if REV_CNTR='' then PROVZIP=PRVDR_ZIP;
if REV_CNTR ne '' then PROVZIP=ZIP_CD;

run;

proc sort data = phy_em_&YEAR.;
by BASEID CLAIMID;
run;

data temp.phy_em_&YEAR.;
set phy_em_&YEAR.;
if PRF_NPI = '' then missing_prf=1; else missing_prf=0;
run;

proc sort data = temp.phy_em_&YEAR. nodupkey; /* shouldn't be any dupes */
by BASEID CLAIMID LINEITEM PRF_NPI;
run;

/* Now count again the number of missing provider numbers - so that I know the proportion of E&M visit line items that will need to be dropped */
proc means data = temp.phy_em_&YEAR. n sum mean;
var missing_prf;
title "missing provider numbers for E and M claims - &YEAR. ";
run;
